package cn.yunhe.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.dao.TopicDao;
import cn.yunhe.entity.Board;
import cn.yunhe.entity.Topic;
import cn.yunhe.entity.User;
import cn.yunhe.util.C3P0Util;

public class TopicDaoImpl implements TopicDao {
	/**
	 * 发主帖
	 */
	@Override
	public boolean addTopic(Topic topic) {
		int num = -1;
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into tb_topic(title,content,create_time,uid,bid) ");
		sql.append(" values(?,?,?,?,?)");
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, topic.getTitle());
			ps.setString(2, topic.getContent());
			ps.setString(3, topic.getCreateTime());
			ps.setInt(4, topic.getUser().getId());
			ps.setInt(5, topic.getBoard().getId());
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			C3P0Util.close(null, ps, conn);
		}

	}

	@Override
	public List<Topic> findTopicsByBoardId(int bid) {
		List<Topic> list = new ArrayList<Topic>();
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" select t.tid,t.title,t.content,t.create_time,u.uid,u.uname,bid ");
		sql.append(" from tb_topic t ,tb_user u ");
		sql.append(" where bid = ? and t.uid = u.uid");
		sql.append(" order by create_time asc");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, bid);
			rs = ps.executeQuery();
			while (rs.next()) {
				Topic t = new Topic();
				t.setId(rs.getInt("tid"));
				t.setTitle(rs.getString("title"));
				t.setContent(rs.getString("content"));
				t.setCreateTime(rs.getString("create_time"));
				t.setUser(new User(rs.getInt("uid"), rs.getString("uname")));
				t.setBoard(new Board(rs.getInt("bid")));
				list.add(t);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return list;
		} finally {
			C3P0Util.close(rs, ps, conn);
		}
	}

	@Override
	public List<Topic> findTopicsByUserId(int uid) {
		List<Topic> list = new ArrayList<Topic>();
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" select tid,title,content,create_time,uid,bid ");
		sql.append(" from tb_topic");
		sql.append(" where uid = ?");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			rs = ps.executeQuery();
			while (rs.next()) {
				Topic t = new Topic();
				t.setId(rs.getInt("tid"));
				t.setTitle(rs.getString("title"));
				t.setContent(rs.getString("content"));
				t.setCreateTime(rs.getString("create_time"));
				list.add(t);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return list;
		} finally {
			C3P0Util.close(rs, ps, conn);
		}
	}

	@Override
	public Topic getTopicByTid(int tid) {
		Topic topic = null;
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" select t.tid,t.title,t.content,t.create_time,u.uid,u.uname,b.bid ,b.bname");
		sql.append(" from tb_topic t , tb_user u , tb_board b");
		sql.append(" where t.uid=u.uid and t.bid = b.bid and tid = ?");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, tid);
			rs = ps.executeQuery();
			if (rs.next()) {
				topic = new Topic();
				topic.setId(rs.getInt("tid"));
				topic.setTitle(rs.getString("title"));
				topic.setContent(rs.getString("content"));
				topic.setCreateTime(rs.getString("create_time"));
				topic.setUser(new User(rs.getInt("uid"),rs.getString("uname")));
				topic.setBoard(new Board(rs.getInt("bid")));
			}
			return topic;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return topic;
		} finally {
			C3P0Util.close(rs, ps, conn);
		}
	}

	@Override
	public boolean deleteTopic(int tid,int uid) {
		int num = -1;
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" update tb_topic ");
		sql.append(" set is_deleted = 1");
		sql.append(" where tid = ? and uid=?");
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, tid);
			ps.setInt(2, uid);
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			C3P0Util.close(null, ps, conn);
		}

	}

	@Override
	public List<Topic> findTopicsByReplyUserId(int uid) {
		List<Topic> list = new ArrayList<Topic>();
		Connection conn = C3P0Util.getConnection();
		StringBuilder sql = new StringBuilder();
		sql.append(" select t.tid,t.title,t.content,t.create_time,t.uid,t.bid ");
		sql.append(" from tb_topic t , tb_reply  r");
		sql.append(" where t.tid = r.tid and r.uid = ?");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			rs = ps.executeQuery();
			while (rs.next()) {
				Topic t = new Topic();
				t.setId(rs.getInt("tid"));
				t.setTitle(rs.getString("title"));
				t.setContent(rs.getString("content"));
				t.setCreateTime(rs.getString("create_time"));
				list.add(t);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return list;
		} finally {
			C3P0Util.close(rs, ps, conn);
		}
	}
}
